package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Student;
import cn.edu.sdjzu.xg.bysj.domain.Teacher;
import cn.edu.sdjzu.xg.bysj.service.TeacherService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class StudentDao {

    private static StudentDao studentDao = new StudentDao();


    private StudentDao() {
    }

    public static StudentDao getInstance() {
        return studentDao;
    }

    public static Collection<Student> findAll(Teacher teacher,
                                              Pagination pagination,
                                              Connection connection) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Student对象0
        Collection<Student> students = new TreeSet<Student>();
        //创建查询的主语句
        int totalNum = CommonDao.getInstance().count(connection,"student");
        StringBuilder sql_select = new StringBuilder("SELECT * FROM student WHERE teacher_id = ? ");
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }

        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        pstmt.setInt(1, teacher.getId());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
            students.add(new Student(resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks" ),
                    TeacherService.getInstance().find(resultSet.getInt("teacher_id")))
            );
        }
        return students;
    }

    public static Collection<Student> findAll(List conditionList,
                                              Pagination pagination,
                                              Connection connection) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Student对象
        Collection<Student> students = new TreeSet<Student>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection,"student");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM Student ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
             students.add(new Student(resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks" ),
                     TeacherService.getInstance().find(resultSet.getInt("teacher_id")))
            );
        }
        return students;
    }

    public static Student find(int id ,Connection connection) throws SQLException {

        //创建sql语句
        String find_sql = "select * from student where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        Student student = null;
        //游标下移
        if(resultSet.next()) {
            //创建Student对象，各字段为结果集中相应字段
             student = new Student(resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks"),
                    TeacherService.getInstance().find(resultSet.getInt("teacher_id"))
            );
        }
        return student;
    }
    public static boolean update(Student student,Connection connection) throws SQLException {

        //创建sql语句
        String updateSchool_sql = "update student set name=?,no=?,remarks = ?,teacher_id=? where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(5,student.getId());
        pstmt.setString(1,student.getName());
        pstmt.setString(2,student.getNo());
        pstmt.setString(3,student.getRemarks());
        pstmt.setInt(4,student.getSupervisor().getId());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            return false;
        }else
            return  true;
    }

    public static boolean add(Student student, Connection connection) throws SQLException {
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement preparedStatement =
                connection.prepareStatement(
                        "INSERT INTO student " +
                                " ( id,name,no,remarks)" +
                                " VALUES (?, ?, ?,?)");
        preparedStatement.setInt(1, student.getId());
        preparedStatement.setString(2, student.getName());
        preparedStatement.setString(3, student.getNo());
        preparedStatement.setString(4, student.getRemarks());
        int a = preparedStatement.executeUpdate();
        //本方法是事务的一部分，不能释放资源
        JdbcHelper.close(preparedStatement,null);
        return a>0;
    }

    public static boolean delete(int id,Connection connection) throws SQLException{

        //创建sql语句
        String deleteStudent_sql = "DELETE FROM student WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteStudent_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        if (pstmt.executeLargeUpdate() == 1){
            return true;
        }else {
            return false;
        }
    }
    public static boolean delete(Student student,Connection connection) throws SQLException {
        String deleteTeacher_sql = "delete from student where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteTeacher_sql );
        //为预编译参数赋值
        pstmt.setString(1,student.getNo());
        //判断是否删除成功
        if(pstmt.executeUpdate() == 0){
            return false;
        }else {
            return true;
        }
    }
    public static int countAll(Teacher teacher) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        PreparedStatement pstmt = connection.prepareStatement("SELECT COUNT(id) as cnt_by_teacher FROM student WHERE teacher_id=?");
        pstmt.setInt(1,teacher.getId());
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        int result = resultSet.getInt("cnt_by_teacher");
        JdbcHelper.close(resultSet,pstmt,connection);
        return result;
    }
}